using System;
using System.Collections;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HermesPortal.Core
{
    internal  class ChapterDAO :BaseDAO
    {
        #region Chapters 
        internal static Chapter GetChapter(int sagaID, int chapterID, int creatorID)
        {
            Chapter chapter = null;
            SqlDataReader   reader = null; 
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChapterGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                 cmd.Parameters.AddWithValue("@ChapterID", chapterID);
                 cmd.Parameters.AddWithValue("@CreatorID", creatorID);
                conn.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    chapter = new Chapter(reader); 
                }
                return chapter; 

            }
        }

        internal static Chapter GetChapterAdmin(int sagaID, int chapterID)
        {
            Chapter chapter = null;
            SqlDataReader reader = null; 
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChapterGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@ChapterID", chapterID);
                conn.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                     chapter = new Chapter(reader);
                }
                return chapter;

            }
        }

        internal static List<Chapter> GetChapters(int sagaID, int creatorID)
        {
            List<Chapter> chapters = new List<Chapter>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChaptersGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@CreatorID", creatorID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    chapters.Add(new Chapter(reader));
                }

            }

            return chapters;
        }

        internal static List<Chapter> GetChaptersAdmin(int sagaID)
        {
            List<Chapter> chapters = new List<Chapter>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChaptersGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    chapters.Add(new Chapter(reader));
                }

            }

            return chapters;
        }

        internal static void UpdateChapter(Chapter chapter)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChapterUpdate", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ChapterID", chapter.Id);
                cmd.Parameters.AddWithValue("@Title", chapter.Title);
                cmd.Parameters.AddWithValue("@Header", chapter.Header);
                cmd.Parameters.AddWithValue("@Footer", chapter.Footer);
                cmd.Parameters.AddWithValue("@StartMonth", chapter.StartMonth);
                cmd.Parameters.AddWithValue("@EndMonth", chapter.EndMonth);
                cmd.Parameters.AddWithValue("@StartYear", chapter.StartYear);
                cmd.Parameters.AddWithValue("@EndYear", chapter.EndYear);
                cmd.Parameters.AddWithValue("@ViewOrder", chapter.ViewOrder);
                cmd.Parameters.AddWithValue("@AccessType", chapter.AccessType);
                cmd.Parameters.AddWithValue("@OwnerID", chapter.OwnerID);
                cmd.Parameters.AddWithValue("@IsPrivate", chapter.IsPrivate);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        internal static int InsertChapter(Chapter chapter)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChapterInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", chapter.CreatorID);
                cmd.Parameters.AddWithValue("@SagaID", chapter.SagaID);
                cmd.Parameters.AddWithValue("@Title", chapter.Title);
                cmd.Parameters.AddWithValue("@Header", chapter.Header);
                cmd.Parameters.AddWithValue("@Footer", chapter.Footer);
                cmd.Parameters.AddWithValue("@StartMonth", chapter.StartMonth);
                cmd.Parameters.AddWithValue("@EndMonth", chapter.EndMonth);
                cmd.Parameters.AddWithValue("@StartYear", chapter.StartYear);
                cmd.Parameters.AddWithValue("@EndYear", chapter.EndYear);
                cmd.Parameters.AddWithValue("@ViewOrder", chapter.ViewOrder);
                cmd.Parameters.AddWithValue("@AccessType", chapter.AccessType);
                cmd.Parameters.AddWithValue("@OwnerID", chapter.OwnerID);
                cmd.Parameters.AddWithValue("@IsPrivate", chapter.IsPrivate);

                conn.Open();
               object objChapterID = cmd.ExecuteScalar();
                conn.Close();
                return Convert.ToInt32(objChapterID); 
            }
        }

        /// <summary>
        ///  Updates the DB with an existing chapters new ordinal value. 
        /// </summary>
        /// <param name="chapterID">The chapter that changed</param>
        /// <param name="sagaID">the saga the chapter belings to.</param>
        /// <param name="ordinal">the updated ordinal value</param>
        internal static void ChapterOrderUpdate(int chapterID, int sagaID, int ordinal  )
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                 SqlCommand cmd = new SqlCommand("ChapterSetNewOrder", conn);
                 cmd.Parameters.AddWithValue("@ChapterID", chapterID);
                 cmd.Parameters.AddWithValue("@SagaID", sagaID);
                 cmd.Parameters.AddWithValue("@Ordinal", ordinal);
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
            }
        }

        internal static void DeleteChapter(int chapterID )
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChapterDelete", conn);
                cmd.Parameters.AddWithValue("@ChapterID", chapterID);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        #endregion

        #region Sections
    
        internal static List<Section> GetSections(int sagaID, int creatorID)
        {
            List<Section> section = new List<Section>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ChaptersGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@CreatorID", creatorID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    section.Add(new Section(reader));
                }

            }

            return section;
        }

        internal static List<Section> GetSectionsAdmin(int chapterID)
        {
            List<Section>  sections = null;
            SqlDataReader reader = null;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SectionsGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ChapterID", chapterID);
                conn.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    sections.Add(new Section(reader));
                }
              

            }
            return sections;
        }

        internal static int InsertSection(Section section)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SectionInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", section.CreatorID);
                cmd.Parameters.AddWithValue("@ChapterID", section.ChapterID );
                cmd.Parameters.AddWithValue("@Body", section.Body);
                cmd.Parameters.AddWithValue("@ImagePath", section.ImagePath);
                cmd.Parameters.AddWithValue("@ViewOrder", section.ViewOrder);
                cmd.Parameters.AddWithValue("@AccessID", section.AccessID);
                cmd.Parameters.AddWithValue("@IsPrivate", section.IsPrivate);

                conn.Open();
                object objsectionid = cmd.ExecuteScalar();
                conn.Close();
                return Convert.ToInt32(objsectionid);
            }
            
        }
        internal static void UpdateSection(Section section)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SectionUpdate", conn);
                cmd.Parameters.AddWithValue("@SectionID", section.Id  );
                cmd.Parameters.AddWithValue("@ChapterID", section.ChapterID );
                cmd.Parameters.AddWithValue("@Body", section.Body);
                cmd.Parameters.AddWithValue("@CreatorID", section.CreatorID);
                cmd.Parameters.AddWithValue("@ImagePath", section.ImagePath);
                cmd.Parameters.AddWithValue("@AccessID", section.AccessID  );
                cmd.Parameters.AddWithValue("@IsPrivate", section.IsPrivate);
                
                
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
          
        }

      

        internal static void DeleteSection(int sectionID)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SectionDelete", conn);
                cmd.Parameters.AddWithValue("@SectionID", sectionID);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        internal static void UpdateViewOrder(int sectionID, int viewOrder)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SectionUpdateViewOrder", conn);
                cmd.Parameters.AddWithValue("@SectionID", sectionID);
                cmd.Parameters.AddWithValue("@ChapterID", viewOrder);
                

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        #endregion

        #region Comments
        internal static List<ChapterComment> GetComments(ChapterComment comment, int clientId )
        {
            return null;
        }

        internal static List<ChapterComment> GetCommentsAdmin(ChapterComment comment)
        {
            return null;
        }

        internal static int InsertComment(ChapterComment comment)
        {
            return 0;
        }
        internal static void UpdateComment(ChapterComment comment)
        {

        }

        internal static void DeleteComment(int sectionId)
        {

        }


        #endregion
    }
}
